﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OleDb;
using DTO;

namespace DAO
{
    public class PhieuDatDAO
    {
        public string LayMaDHLonNhat()
        {
            string sql = "SELECT TOP 1, MaPD FROM PhieuDat ORDER BY MaPD DESC";
            OleDbConnection conn = new OleDbConnection();
            conn = DataProvider.ConnectDB();
            OleDbCommand cmd = new OleDbCommand(sql, conn);
            OleDbDataReader reader = cmd.ExecuteReader();
            CTPhieuDatDTO ctpdDTO = new CTPhieuDatDTO();
            while (reader.Read())
            {
                ctpdDTO.MaCTPD = reader.GetString(0);

            }
            conn.Close();
            return ctpdDTO.MaCTPD;
        }
        public string NextID(string lastID, string prefixID)
        {
            if (lastID == null)
            {
                return prefixID + "001";
            }
            int nextID = int.Parse(lastID.Remove(0, prefixID.Length)) + 1;
            int lengthNumberID = lastID.Length - prefixID.Length;
            string zeroNumber = "";
            for (int i = 1; i <= lengthNumberID; i++)
            {
                if (nextID < Math.Pow(10, i))
                {
                    for (int j = 1; j <= lengthNumberID - i; i++)
                    {
                        zeroNumber += "0";
                    }
                    return prefixID + zeroNumber + nextID.ToString();
                }
            }
            return prefixID + nextID;
        }
        public string TaoMaDHMoi()
        {
            string LastID = LayMaDHLonNhat();
            return NextID(LastID, "DH");
        }
        public int ThemPhieuDat(PhieuDatDTO pdDTO)
        {
            OleDbConnection conn = DataProvider.ConnectDB();
            string sql = "INSERT INTO PHIEUDAT(MaPD,NgayDH,MaNV,TongTien) ";
            sql = sql + " VALUES(@MaPD,@NgayDH,@MaNV,@TongTien) ";
            OleDbCommand cmd = new OleDbCommand(sql, conn);
            OleDbParameter para = cmd.Parameters.Add("@MaPD", OleDbType.VarChar);
            para.Value = pdDTO.MaPD;
            para = cmd.Parameters.Add("@NgayDH", OleDbType.DBDate);
            para.Value = pdDTO.NgayDH;
            para = cmd.Parameters.Add("@MaNV", OleDbType.VarChar);
            para.Value = pdDTO.MaNV;
            para = cmd.Parameters.Add("@TongTien", OleDbType.Double);
            para.Value = pdDTO.TongTien;
            int res = cmd.ExecuteNonQuery();
            if (res >= 0)
                return 1;
            return 0;

        }
        public List<PhieuDatDTO> LayMaPhieuDatChuaNhap()
        {

            string sql = "SELECT MaPD FROM PHIEUDAT WHERE MaPD not in (SELECT MAPD FROM PHIEUNHAP)";
            OleDbConnection conn = new OleDbConnection();
            conn = DataProvider.ConnectDB();
            OleDbCommand cmd = new OleDbCommand(sql, conn);
            OleDbDataReader reader = cmd.ExecuteReader();
            List<PhieuDatDTO> DanhSach = new List<PhieuDatDTO>();
            while (reader.Read())
            {
                PhieuDatDTO pdDTO = new PhieuDatDTO();
                pdDTO.MaPD = reader.GetString(0);
                DanhSach.Add(pdDTO);
            }
            conn.Close();
            return DanhSach;
        }
        public List<PhieuDatDTO> TheoDoiDatHang(string Thang, string Nam)
        {
            string sql = "SELECT * FROM PHIEUDAT WHERE Month(NgayDH) = '"+Thang+"' and Year(NgayDH) = '"+Nam+"'";
            OleDbConnection conn = new OleDbConnection();
            conn = DataProvider.ConnectDB();
            OleDbCommand cmd = new OleDbCommand(sql, conn);
            OleDbDataReader reader = cmd.ExecuteReader();
            List<PhieuDatDTO> DanhSach = new List<PhieuDatDTO>();
            while (reader.Read())
            {
                if (reader.IsDBNull(0) || reader.IsDBNull(1) || reader.IsDBNull(2) || reader.IsDBNull(3))
                    DanhSach = null;
                else
                {
                    PhieuDatDTO pdDTO = new PhieuDatDTO();
                    pdDTO.MaPD = reader.GetString(0);
                    pdDTO.NgayDH = reader.GetDateTime(1);
                    pdDTO.MaNV = reader.GetString(2);
                    pdDTO.TongTien = reader.GetDouble(3);
                    DanhSach.Add(pdDTO);
                }

             
            }
            conn.Close();
            return DanhSach;
        }
        public List<PhieuDatDTO> LayPhieuDatChuaNhap()
        {
            string sql = "SELECT * FROM PHIEUDAT WHERE MaPD not in(SELECT MaPD FROM PHIEUNHAP)";
            OleDbConnection conn = new OleDbConnection();
            conn = DataProvider.ConnectDB();
            OleDbCommand cmd = new OleDbCommand(sql, conn);
            OleDbDataReader reader = cmd.ExecuteReader();
            List<PhieuDatDTO> DanhSach = new List<PhieuDatDTO>();
            while (reader.Read())
            {
                if (reader.IsDBNull(0) || reader.IsDBNull(1) || reader.IsDBNull(2) || reader.IsDBNull(3))
                    DanhSach = null;
                else
                {
                    PhieuDatDTO pdDTO = new PhieuDatDTO();
                    pdDTO.MaPD = reader.GetString(0);
                    pdDTO.NgayDH = reader.GetDateTime(1);
                    pdDTO.MaNV = reader.GetString(2);
                    pdDTO.TongTien = reader.GetDouble(3);
                    DanhSach.Add(pdDTO);
                }


            }
            conn.Close();
            return DanhSach;
        }


    }
}
